#!/usr/bin/python
# -*- coding:utf-8 -*-
import re
from PySide2 import QtWidgets, QtGui, QtCore
from PySide2.QtWidgets import QFileDialog, QMessageBox, QComboBox, QCheckBox, QLineEdit, QTableView
from addDatabaseTable_ui import Ui_MainWindow
import sqlite3


class addDatabaseTableWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self, conn, main_window):
        super().__init__()
        self.main_window = main_window  # 保存主窗口的引用
        self.setupUi(self)
        self.conn = conn
        self.db_file_path = ""  # 数据库文件路径
        self.tables = []  # 数据库中的数据表名字
        self.table_descriptions = []  # 数据库中已存在的表
        # 将按钮的点击事件连接到自定义函数
        self.addFiledPushButton.clicked.connect(self.add_field)
        self.confirmPushButton.clicked.connect(self.confirm_create_table)
        self.delFiledPushButton.clicked.connect(self.delete_field)
        self.closePushButton.clicked.connect(self.closeWindow)

        # 初始化模型
        self.model_addDB = QtGui.QStandardItemModel()  # 添加数据表模型
        self.model_database_table = QtGui.QStandardItemModel()  # 已有数据库表模型
        # 预加载函数
        self.create_connection()
        self.set_model_to_addDB()
        self.set_model_existing_database()


    '''
    设置已有数据表-模型
    '''

    def set_model_existing_database(self):
        # 清除现有数据，防止重复添加
        self.model_database_table.clear()
        # 设置model
        headers = ["表名", "备注"]
        for i, header in enumerate(headers):
            self.model_database_table.setHorizontalHeaderItem(i, QtGui.QStandardItem(header))

        self.tableView_2.setModel(self.model_database_table)

        # 填充模型
        for table_name, description in self.table_descriptions:
            row = [
                QtGui.QStandardItem(table_name),
                QtGui.QStandardItem(description)
            ]
            self.model_database_table.appendRow(row)

        # 建立 view 和 model 的联系
        self.tableView_2.setModel(self.model_database_table)
        # 设置列宽自适应表格宽度
        self.tableView_2.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
    '''
    设置模型--
    '''

    def set_model_to_addDB(self):
        # 设置model
        headers = ["字段名", "数据类型", "长度", "小数点", "是否为null", "是否为主键"]
        for i, header in enumerate(headers):
            self.model_addDB.setHorizontalHeaderItem(i, QtGui.QStandardItem(header))

        # 设置列宽自适应表格宽度
        self.tableAdd.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)

        # 建立 view 和 model 的联系
        self.tableAdd.setModel(self.model_addDB)

    # '''
    # 选择数据库
    # '''
    #
    # def open_file_dialog(self):
    #     if self.sender() == self.pushButton_5:
    #         # 使用提供的样式打开文件对话框，并且只显示 .db 文件
    #         file_name, _ = QFileDialog.getOpenFileName(self, "选择文件", "", "数据库文件 (*.db)")
    #         if file_name:
    #             self.lineEdit_3.setText(file_name)
    #             print(f"你选择的文件是: {file_name}")
    #             self.db_file_path = file_name
    #             self.create_connection()

    '''
    连接数据库
    '''

    def create_connection(self):
        try:
            # conn = sqlite3.connect(self.db_file_path)
            print("成功连接到数据库")
            self.db_file_path = self.conn
            self.get_table_info(self.conn)
        except sqlite3.Error as e:
            print(f"连接到数据库失败: {e}")

    '''
    获取数据库中的表信息
    '''

    def get_table_info(self, conn):
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            self.tables = cursor.fetchall()
            self.tables = {table[0] for table in self.tables}  # 提取每个元组的第一个元素并存储在集合中
            print(self.tables)
            for table in self.tables:
                print(table)

            # 获取所有表的描述信息
            cursor.execute("SELECT table_name, description FROM table_descriptions;")
            self.table_descriptions = cursor.fetchall()
            print(self.table_descriptions)
            # self.table_descriptions = {table_description[0] for table_description in self.table_descriptions}  # 提取每个元组的第一个元素并存储在集合中

            self.set_model_existing_database()
            for table_description in self.table_descriptions:
                print(table_description)
        except sqlite3.Error as e:
            print(f"获取表信息失败: {e}")
        finally:
            if conn:
                pass
                # conn.close()

    '''
    添加字段
    '''

    def add_field(self):
        row = self.model_addDB.rowCount()
        self.model_addDB.setItem(row, 0, QtGui.QStandardItem())
        self.model_addDB.setItem(row, 1, QtGui.QStandardItem())
        self.model_addDB.setItem(row, 2, QtGui.QStandardItem())
        self.model_addDB.setItem(row, 3, QtGui.QStandardItem())
        self.model_addDB.setItem(row, 4, QtGui.QStandardItem())
        self.model_addDB.setItem(row, 5, QtGui.QStandardItem())

        # 在每个单元格中插入合适的控件
        field_name = QLineEdit()
        data_type = QComboBox()
        data_type.addItems(["INTEGER", "TEXT", "REAL", "BLOB"])  # 示例数据类型
        length = QLineEdit()
        decimal = QLineEdit()
        is_null = QCheckBox()
        is_primary = QCheckBox()

        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 0), field_name)
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 1), data_type)
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 2), length)
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 3), decimal)
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 4), is_null)
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 5), is_primary)

        # 连接信号槽
        data_type.currentIndexChanged.connect(lambda index, cb=data_type: self.data_type_changed(cb, row))
        is_primary.stateChanged.connect(lambda state, r=row: self.primary_key_changed(is_primary, is_null, r))

    # 数据类型改变
    def data_type_changed(self, combo_box, row):
        data_type = combo_box.currentText()
        length_widget = self.tableAdd.indexWidget(self.model_addDB.index(row, 2))
        decimal_widget = self.tableAdd.indexWidget(self.model_addDB.index(row, 3))
        if data_type in ["INTEGER", "TEXT", "BLOB"]:
            length_widget.setText("不需要填写")
            decimal_widget.setText("不需要填写")
            length_widget.setEnabled(False)
            decimal_widget.setEnabled(False)
        elif data_type == "REAL":
            length_widget.setText("")
            decimal_widget.setText("")
            length_widget.setEnabled(True)
            decimal_widget.setEnabled(True)

    # 主键改变
    def primary_key_changed(self, is_primary, is_null, row):
        if is_primary.isChecked():
            # 确保只有一个主键
            for r in range(self.model_addDB.rowCount()):
                if r != row:
                    pk_checkbox = self.tableAdd.indexWidget(self.model_addDB.index(r, 5))
                    if pk_checkbox.isChecked():
                        QMessageBox.warning(self, "Warning", "只能有一个主键字段。")
                        is_primary.setChecked(False)
                        return

            # 设置是否为null为否且不可选
            is_null.setChecked(False)
            is_null.setEnabled(False)
        else:
            # 重新允许设置是否为null
            is_null.setEnabled(True)

    '''
    删除字段
    '''

    def delete_field(self):
        selection_model = self.tableAdd.selectionModel()
        indexes = selection_model.selectedIndexes()
        if indexes:
            for index in indexes:
                self.model_addDB.removeRow(index.row())

    '''
    确认创建表
    '''

    def confirm_create_table(self):
        table_name = self.addTable.text()
        if self.db_file_path == "":
            QMessageBox.warning(self, "Warning", "请先连接数据库再创建表!")
            return
        if table_name == "":
            QMessageBox.warning(self, "Warning", "请输入要创建的表名!")
            return
        if table_name in self.tables:
            QMessageBox.warning(self, "Warning", "数据库中已存在同名表!")
            return
        # 检查是否至少有一个主键
        has_primary_key = False
        field_definitions = []
        row_count = self.model_addDB.rowCount()

        for row in range(row_count):
            field_name = self.tableAdd.indexWidget(self.model_addDB.index(row, 0)).text()
            data_type = self.tableAdd.indexWidget(self.model_addDB.index(row, 1)).currentText()
            length = self.tableAdd.indexWidget(self.model_addDB.index(row, 2)).text()
            decimal = self.tableAdd.indexWidget(self.model_addDB.index(row, 3)).text()
            is_null = self.tableAdd.indexWidget(self.model_addDB.index(row, 4)).isChecked()
            is_primary = self.tableAdd.indexWidget(self.model_addDB.index(row, 5)).isChecked()

            # 构建字段定义
            field_definition = f"{field_name} {data_type}"
            if length and length != "不需要填写":
                field_definition += f"({length})"
            if decimal and decimal != "不需要填写":
                field_definition += f"({length},{decimal})"
            if not is_null:
                field_definition += " NOT NULL"
            if is_primary:
                field_definition += " PRIMARY KEY"

            field_definitions.append(field_definition)

        if is_primary:
            has_primary_key = True

        print(f"{field_name}  {data_type} {length} {decimal} {is_null} {is_primary}")

        # if not has_primary_key:
        #     QMessageBox.warning(self, "Warning", "需要一个字段设置为主键!")
        #     return

        print("field_definitions")
        print(field_definitions)

        # 构建CREATE TABLE语句
        create_table_sql = f"CREATE TABLE IF NOT EXISTS {self.addTable.text()} ({', '.join(field_definitions)});"
        # 关于表的描述
        create_description_table_sql = """
                CREATE TABLE IF NOT EXISTS table_descriptions (
                    table_name TEXT PRIMARY KEY,
                    description TEXT
                );
                """

        # 执行SQL语句
        try:
            # conn = sqlite3.connect(self.conn)
            cursor = self.conn.cursor()
            cursor.execute(create_table_sql)
            cursor.execute(create_description_table_sql)
            cursor.execute("INSERT INTO table_descriptions (table_name, description) VALUES (?, ?)",
                           (self.addTable.text(), self.describe.text()))
            self.conn.commit()
            QMessageBox.information(self, "Success", "Table created successfully.")
            # 重新重数据库获取一下数据表名字
            self.create_connection()
            # 刷新导入数据主窗口的信息
            self.main_window.get_tables()
        except Exception as e:
            QMessageBox.critical(self, "Error", str(e))
        finally:
            pass
            # conn.close()

    '''
    关闭窗口
    '''

    def closeWindow(self):
        self.close()
